-- @owner: @zou_jialiang050
-- @date: 2023/5/10
-- @testpoint: 用户已存在时在自定义函数中测试语法

--step1:创建普通用户;expect:成功
set b_compatibility_user_host_auth = on;
drop user if exists u_grant_case019_1 cascade;
drop user if exists u_grant_case019_2@localhost cascade;
drop user if exists u_grant_case019_3@123.123.123.123 cascade;
drop user if exists u_grant_case019_4@localhost cascade;
drop user if exists u_grant_case019_5@localhost cascade;
drop user if exists u_grant_case019_6@localhost cascade;
create user u_grant_case019_1 password 'test@123';
create user u_grant_case019_2@localhost password 'test@123';
create user u_grant_case019_3@123.123.123.123 password 'test@123';
create user u_grant_case019_4@localhost password 'test@123';
create user u_grant_case019_5@localhost password 'test@123';
create user u_grant_case019_6@localhost password 'test@123';

--step2:创建自定义函数并调用;expect:成功
create or replace function f_grant_case019() returns int as $$
declare i int default 0;
begin
while i < 1 do
    grant usage on *.* to u_grant_case019_1 identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
    grant usage on *.* to u_grant_case019_2@localhost identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
    grant usage on *.* to u_grant_case019_3@123.123.123.123 identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
    grant usage on *.* to 'u_grant_case019_4'@localhost identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
    grant usage on *.* to u_grant_case019_5@'localhost' identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
    grant usage on *.* to 'u_grant_case019_6'@'localhost' identified by 'Qq1@~!#$%^&*-_=+|[{()}];:,<.>/?';
set i = i + 1;
end while;
return i;
end;
$$ language plpgsql;
/
call f_grant_case019();
select usename from pg_user where usename like 'u_grant_case019%' order by usename;

--step3:清理环境;expect:成功
drop user if exists u_grant_case019_1 cascade;
drop user if exists u_grant_case019_2@localhost cascade;
drop user if exists u_grant_case019_3@123.123.123.123 cascade;
drop user if exists u_grant_case019_4@localhost cascade;
drop user if exists u_grant_case019_5@localhost cascade;
drop user if exists u_grant_case019_6@localhost cascade;
reset b_compatibility_user_host_auth;